
CREATE  procedure sp_carteraVencidaSesibilizada60 @fecha datetime  
as

create table #tmp_carteraVencida (
	iddesarrollo varchar(3),
	tipodesarrollo int,
	importe_monto_vencido decimal(18,5),
	importe_tipo1 decimal(18,5),
	clientes_tipo1 int,
	importe_tipo2 decimal(18,5),
	clientes_tipo2 int,
	importe_tipo3 decimal(18,5),
	clientes_tipo3 int,
	importe_tipo4 decimal(18,5),
	total_porvencer decimal(18,5),
	suma decimal(18,5),
	cartera_documentada decimal(18,5),
	cartera_documentadaI decimal(18,5),
	cartera_documentadaT decimal(18,5),
	carteraxcobrar_vigente decimal(18,5),
	porcentaje_cartera_vencida decimal(18,5),
	porcentaje_cartera_vencida_acumulado decimal(18,5),
	porcentaje_cartera_total_documentada decimal(18,5),
	porcentaje_creditos_vencidos decimal(18,5)
)

insert into #tmp_carteraVencida (
	iddesarrollo,
	tipodesarrollo
)
select	iddesarrollo,
	tipodesarrollo
from	desarrollos

update	#tmp_carteraVencida
set	importe_tipo1 = total,
	clientes_tipo1 = no_clientes
from	(
	select	lote_con_dias_atraso.iddesarrollo,
		TipoDesarrollo,
		sum( importe ) as total,
		count( * ) as no_clientes
	from
	(
		select	iddesarrollo,
			tipodesarrollo,
			cvelote,
			max( dias_atraso_max ) as dias_atraso_max,
			sum( importe ) as importe
		from
		(
			select	 f.iddesarrollo,
				 f.TipoDesarrollo,
				 c.cvelote,
				( 	select 	max( DATEDIFF( dd, Vencimiento, @fecha ) )
					from 	vw_detventa
					where 	cvelote = c.cvelote and
						vencimiento < @fecha and
						( pagada in (null,'N','P') or
						  FechaPago > @fecha ) and
						( idtipointercambio is null or
						  idtipointercambio = 'N' ) and
						 DATEDIFF( dd, Vencimiento, @fecha ) >= 3
				 ) as dias_atraso_max,
				 sum( case
					when c.Pagada = 'P' then C.mensualidad-( isnull( c.deposito, 0 ) + 
isnull( c.descuento, 0 ) - isnull( c.moratorios, 0 ) )
					else c.mensualidad
				 end ) as importe
			from	 desarrollos f
					inner join lotes l
					on f.iddesarrollo = l.iddesarrollo
					inner join ventas v
					on l.idlote = v.idlote and
					   v.EstadoActual = 'A'
					inner join intereses as i
					on i.cvedesarrollo = f.iddesarrollo
					inner join DetVenta as c
					on c.cvelote = l.idlote
			where	 l.staventa = 'C' and
				 ( c.pagada in (null,'N','P') or
				   c.FechaPago > @fecha ) and
				 ( v.idtipointercambio is null or
				   v.idtipointercambio = 'N' ) and
				 c.vencimiento < @fecha and
				 DATEDIFF( dd, c.Vencimiento, @fecha ) >= 3
			group by f.iddesarrollo,
				 f.TipoDesarrollo,
				 c.cvelote
			union
			select	 f.iddesarrollo,
				 f.TipoDesarrollo,
				 e.cvelote,
				( 	select 	max( DATEDIFF( dd, Vencimiento, @fecha ) )
					from 	vw_enganches
					where 	cvelote = e.cvelote and
						vencimiento < @fecha and
						( pagada in (null,'N','P') or
						  FechaPago > @fecha ) and
						( idtipointercambio is null or
						  idtipointercambio = 'N' ) and
						 DATEDIFF( dd, Vencimiento, @fecha )  >= 3
				 ) as dias_atraso_max,
				 sum( case
					when e.Pagada = 'P' then e.importe-( isnull( e.deposito, 0 ) + isnull( 
e.descuento, 0 ) - isnull( e.moratorios, 0 ) )
					else e.importe
				 end ) as importe
			from	 desarrollos f
					inner join lotes l
					on f.iddesarrollo = l.iddesarrollo
					inner join ventas v
					on l.idlote = v.idlote and
					   v.EstadoActual = 'A'
					inner join Enganches as e
					on e.cvelote = l.idlote
			where	 l.staventa = 'C' and
				 ( e.pagada in (null,'N','P') or
				   e.FechaPago > @fecha ) and
				 ( v.idtipointercambio is null or
				   v.idtipointercambio = 'N' ) and
				 e.fechavento < @fecha and
				 DATEDIFF( dd, e.fechavento, @fecha ) >= 3
			group by f.iddesarrollo,
				 f.TipoDesarrollo,
				 e.cvelote
		) as tabla
		group by iddesarrollo,
			tipodesarrollo,
			cvelote
	) as lote_con_dias_atraso
	where	( case
			when dias_atraso_max between 3 and 30 then 1
			when dias_atraso_max between 31 and 60 then 2
			else 3
		 end ) = 1
	group by lote_con_dias_atraso.iddesarrollo,
		 TipoDesarrollo,
		 case
			when dias_atraso_max between 3 and 30 then 1
			when dias_atraso_max between 31 and 60 then 2
			else 3
		 end
) as datos
where	datos.iddesarrollo collate database_default = #tmp_carteraVencida.iddesarrollo

update	#tmp_carteraVencida
set	importe_tipo2 = total,
	clientes_tipo2 = no_clientes
from	(
	select	lote_con_dias_atraso.iddesarrollo,
		TipoDesarrollo,
		sum( importe ) as total,
		count( * ) as no_clientes
	from
	(
		select	iddesarrollo,
			tipodesarrollo,
			cvelote,
			max( dias_atraso_max ) as dias_atraso_max,
			sum( importe ) as importe
		from
		(
			select	 f.iddesarrollo,
				 f.TipoDesarrollo,
				 c.cvelote,
				( 	select 	max( DATEDIFF( dd, Vencimiento, @fecha ) )
					from 	vw_detventa
					where 	cvelote = c.cvelote and
						vencimiento < @fecha and
						( pagada in (null,'N','P') or
						  FechaPago > @fecha ) and
						( idtipointercambio is null or
						  idtipointercambio = 'N' ) and
						 DATEDIFF( dd, Vencimiento, @fecha ) >= 3
				 ) as dias_atraso_max,
				 sum( case
					when c.Pagada = 'P' then C.mensualidad-( isnull( c.deposito, 0 ) + 
isnull( c.descuento, 0 ) - isnull( c.moratorios, 0 ) )
					else c.mensualidad
				 end ) as importe
			from	 desarrollos f
					inner join lotes l
					on f.iddesarrollo = l.iddesarrollo
					inner join ventas v
					on l.idlote = v.idlote and
					   v.EstadoActual = 'A'
					inner join intereses as i
					on i.cvedesarrollo = f.iddesarrollo
					inner join DetVenta as c
					on c.cvelote = l.idlote
			where	 l.staventa = 'C' and
				 ( c.pagada in (null,'N','P') or
				   c.FechaPago > @fecha ) and
				 ( v.idtipointercambio is null or
				   v.idtipointercambio = 'N' ) and
				 c.vencimiento < @fecha and
				 DATEDIFF( dd, c.Vencimiento, @fecha ) >= 3
			group by f.iddesarrollo,
				 f.TipoDesarrollo,
				 c.cvelote
			union
			select	 f.iddesarrollo,
				 f.TipoDesarrollo,
				 e.cvelote,
				( 	select 	max( DATEDIFF( dd, Vencimiento, @fecha ) )
					from 	vw_enganches
					where 	cvelote = e.cvelote and
						vencimiento < @fecha and
						( pagada in (null,'N','P') or
						  FechaPago > @fecha ) and
						( idtipointercambio is null or
						  idtipointercambio = 'N' ) and
						 DATEDIFF( dd, Vencimiento, @fecha ) >= 3
				 ) as dias_atraso_max,
				 sum( case
					when e.Pagada = 'P' then e.importe-( isnull( e.deposito, 0 ) + isnull( 
e.descuento, 0 ) - isnull( e.moratorios, 0 ) )
					else e.importe
				 end ) as importe
			from	 desarrollos f
					inner join lotes l
					on f.iddesarrollo = l.iddesarrollo
					inner join ventas v
					on l.idlote = v.idlote and
					   v.EstadoActual = 'A'
					inner join Enganches as e
					on e.cvelote = l.idlote
			where	 l.staventa = 'C' and
				 ( e.pagada in (null,'N','P') or
				   e.FechaPago > @fecha ) and
				 ( v.idtipointercambio is null or
				   v.idtipointercambio = 'N' ) and
				 e.fechavento < @fecha and
				 DATEDIFF( dd, e.fechavento, @fecha ) >= 3
			group by f.iddesarrollo,
				 f.TipoDesarrollo,
				 e.cvelote
		) as tabla
		group by iddesarrollo,
			tipodesarrollo,
			cvelote
	) as lote_con_dias_atraso
	where	( case
			when dias_atraso_max between 3 and 30 then 1
			when dias_atraso_max between 31 and 60 then 2
			else 3
		 end ) = 2
	group by lote_con_dias_atraso.iddesarrollo,
		 TipoDesarrollo,
		 case
			when dias_atraso_max between 3 and 30 then 1
			when dias_atraso_max between 31 and 60 then 2
			else 3
		 end
) as datos
where	datos.iddesarrollo collate database_default = #tmp_carteraVencida.iddesarrollo

update	#tmp_carteraVencida
set	importe_tipo3 = total,
	clientes_tipo3 = no_clientes
from	(
	select	lote_con_dias_atraso.iddesarrollo,
		TipoDesarrollo,
		sum( importe ) as total,
		count( * ) as no_clientes
	from
	(
		select	iddesarrollo,
			tipodesarrollo,
			cvelote,
			max( dias_atraso_max ) as dias_atraso_max,
			sum( importe ) as importe
		from
		(
			select	 f.iddesarrollo,
				 f.TipoDesarrollo,
				 c.cvelote,
				( 	select 	max(DATEDIFF( dd, Vencimiento, @fecha ) )
					from 	vw_detventa
					where 	cvelote = c.cvelote and
						vencimiento < @fecha and
						( pagada in (null,'N','P') or
						  FechaPago > @fecha ) and
						( idtipointercambio is null or
						  idtipointercambio = 'N' ) and
						 DATEDIFF( dd, Vencimiento, @fecha ) >= 3
				 ) as dias_atraso_max,
				 sum( case
					when c.Pagada = 'P' then C.mensualidad-( isnull( c.deposito, 0 ) + 
isnull( c.descuento, 0 ) - isnull( c.moratorios, 0 ) )
					else c.mensualidad
				 end ) as importe
			from	 desarrollos f
					inner join lotes l
					on f.iddesarrollo = l.iddesarrollo
					inner join ventas v
					on l.idlote = v.idlote and
					   v.EstadoActual = 'A'
					inner join intereses as i
					on i.cvedesarrollo = f.iddesarrollo
					inner join DetVenta as c
					on c.cvelote = l.idlote
			where	 l.staventa = 'C' and
				 ( c.pagada in (null,'N','P') or
				   c.FechaPago > @fecha ) and
				 ( v.idtipointercambio is null or
				   v.idtipointercambio = 'N' ) and
				 c.vencimiento < @fecha and
				 DATEDIFF( dd, c.Vencimiento, @fecha ) >= 3
			group by f.iddesarrollo,
				 f.TipoDesarrollo,
				 c.cvelote
			union
			select	 f.iddesarrollo,
				 f.TipoDesarrollo,
				 e.cvelote,
				( 	select 	max( DATEDIFF( dd, Vencimiento, @fecha ) )
					from 	vw_enganches
					where 	cvelote = e.cvelote and
						vencimiento < @fecha and
						( pagada in (null,'N','P') or
						  FechaPago > @fecha ) and
						( idtipointercambio is null or
						  idtipointercambio = 'N' ) and
						 DATEDIFF( dd, Vencimiento, @fecha ) >= 3
				 ) as dias_atraso_max,
				 sum( case
					when e.Pagada = 'P' then e.importe-( isnull( e.deposito, 0 ) + isnull( 
e.descuento, 0 ) - isnull( e.moratorios, 0 ) )
					else e.importe
				 end ) as importe
			from	 desarrollos f
					inner join lotes l
					on f.iddesarrollo = l.iddesarrollo
					inner join ventas v
					on l.idlote = v.idlote and
					   v.EstadoActual = 'A'
					inner join Enganches as e
					on e.cvelote = l.idlote
			where	 l.staventa = 'C' and
				 ( e.pagada in (null,'N','P') or
				   e.FechaPago > @fecha ) and
				 ( v.idtipointercambio is null or
				   v.idtipointercambio = 'N' ) and
				 e.fechavento < @fecha and
				 DATEDIFF( dd, e.fechavento, @fecha ) >= 3
			group by f.iddesarrollo,
				 f.TipoDesarrollo,
				 e.cvelote
			) as tabla
		group by iddesarrollo,
			tipodesarrollo,
			cvelote
	) as lote_con_dias_atraso
	where	( case
			when dias_atraso_max between 3 and 30 then 1
			when dias_atraso_max between 31 and 60 then 2
			else 3
		 end ) = 3
	group by lote_con_dias_atraso.iddesarrollo,
		 TipoDesarrollo,
		 case
			when dias_atraso_max between 3 and 30 then 1
			when dias_atraso_max between 31 and 60 then 2
			else 3
		 end
) as datos
where	datos.iddesarrollo collate database_default = #tmp_carteraVencida.iddesarrollo

update	#tmp_carteraVencida
set	importe_tipo4 = importe
from	(
	 select	iddesarrollo,
		sum( importe ) as importe
	 from (
		select	iddesarrollo,
			sum( case
				when d.Pagada = 'P' then d.mensualidad-( isnull( d.deposito, 0 ) + 
isnull( d.descuento, 0 ) - isnull( d.moratorios, 0 ) )
				else d.mensualidad
			end ) as importe
		from	DetVenta d
				inner join (
					select	iddesarrollo,
						cvelote
					from
					(
						select	 f.iddesarrollo,
							 f.TipoDesarrollo,
							 c.cvelote,
							( 	select 	max( DATEDIFF( dd, Vencimiento, @fecha ) )
								from 	vw_detventa
								where 	cvelote = c.cvelote and
									vencimiento < @fecha and
									( pagada in (null,'N','P') or
									  FechaPago > @fecha ) and
									( idtipointercambio is null or
									  idtipointercambio = 'N' ) and
									 DATEDIFF( dd, Vencimiento, @fecha ) >= 3
							 ) as dias_atraso_max,
							 sum( case
								when c.Pagada = 'P' then C.mensualidad-( isnull( c.deposito, 0 ) + 
isnull( c.descuento, 0 ) - isnull( c.moratorios, 0 ) )
								else c.mensualidad
							 end ) as importe
						from	 desarrollos f
								inner join lotes l
								on f.iddesarrollo = l.iddesarrollo
								inner join ventas v
								on l.idlote = v.idlote and
								   v.EstadoActual = 'A'
								inner join DetVenta as c
								on c.cvelote = l.idlote
						where	 l.staventa = 'C' and
							 ( c.pagada in (null,'N','P') or
							   c.FechaPago > @fecha ) and
							 ( v.idtipointercambio is null or
							   v.idtipointercambio = 'N' ) and
							 c.vencimiento < @fecha and
							 DATEDIFF( dd, c.Vencimiento, @fecha ) >= 3
						group by f.iddesarrollo,
							 f.TipoDesarrollo,
							 c.cvelote
					) as lote_con_dias_atraso
					where	dias_atraso_max > 60
					union
					select	iddesarrollo,
						cvelote
					from
					(
						select	 f.iddesarrollo,
							 f.TipoDesarrollo,
							 c.cvelote,
							( 	select 	max( DATEDIFF( dd, Vencimiento, @fecha ) )
								from 	vw_enganches
								where 	cvelote = c.cvelote and
									vencimiento < @fecha and
									( pagada in (null,'N','P') or
									  FechaPago > @fecha ) and
									( idtipointercambio is null or
									  idtipointercambio = 'N' ) and
									 DATEDIFF( dd, Vencimiento, @fecha ) >= 3
							 ) as dias_atraso_max,
							 sum( case
								when c.Pagada = 'P' then C.importe-( isnull( c.deposito, 0 ) + 
isnull( c.descuento, 0 ) - isnull( c.moratorios, 0 ) )
								else c.importe
							 end ) as importe
						from	 desarrollos f
								inner join lotes l
								on f.iddesarrollo = l.iddesarrollo
								inner join ventas v
								on l.idlote = v.idlote and
								   v.EstadoActual = 'A'
								inner join Enganches as c
								on c.cvelote = l.idlote
						where	 l.staventa = 'C' and
							 ( c.pagada in (null,'N','P') or
							   c.FechaPago > @fecha ) and
							 ( v.idtipointercambio is null or
							   v.idtipointercambio = 'N' ) and
							 c.fechavento < @fecha and
							 DATEDIFF( dd, c.fechavento, @fecha ) >= 3
						group by f.iddesarrollo,
							 f.TipoDesarrollo,
							 c.cvelote
					) as lote_con_dias_atraso
					where	dias_atraso_max > 60
				) as por_vencer
				on d.cvelote = por_vencer.cvelote
		where	DATEDIFF( dd, Vencimiento, @fecha ) < 3
		group by iddesarrollo
		union
		select	iddesarrollo,
			sum( case
				when d.Pagada = 'P' then d.importe-( isnull( d.deposito, 0 ) + isnull( 
d.descuento, 0 ) - isnull( d.moratorios, 0 ) )
				else d.importe
			end ) as importe
		from	Enganches d
				inner join (
					select	iddesarrollo,
						cvelote
					from
					(
						select	 f.iddesarrollo,
							 f.TipoDesarrollo,
							 c.cvelote,
							( 	select 	max( DATEDIFF( dd, Vencimiento, @fecha ))
								from 	vw_detventa
								where 	cvelote = c.cvelote and
									vencimiento < @fecha and
									( pagada in (null,'N','P') or
									  FechaPago > @fecha ) and
									( idtipointercambio is null or
									  idtipointercambio = 'N' ) and
									 DATEDIFF( dd, Vencimiento, @fecha ) >= 3
							 ) as dias_atraso_max,
							 sum( case
								when c.Pagada = 'P' then C.mensualidad-( isnull( c.deposito, 0 ) + 
isnull( c.descuento, 0 ) - isnull( c.moratorios, 0 ) )
								else c.mensualidad
							 end ) as importe
						from	 desarrollos f
								inner join lotes l
								on f.iddesarrollo = l.iddesarrollo
								inner join ventas v
								on l.idlote = v.idlote and
								   v.EstadoActual = 'A'
								inner join DetVenta as c
								on c.cvelote = l.idlote
						where	 l.staventa = 'C' and
							 ( c.pagada in (null,'N','P') or
							   c.FechaPago > @fecha ) and
							 ( v.idtipointercambio is null or
							   v.idtipointercambio = 'N' ) and
							 c.vencimiento < @fecha and
							 DATEDIFF( dd, c.Vencimiento, @fecha ) >= 3
						group by f.iddesarrollo,
							 f.TipoDesarrollo,
							 c.cvelote
					) as lote_con_dias_atraso
					where	dias_atraso_max > 60
					union
					select	iddesarrollo,
						cvelote
					from
					(
						select	 f.iddesarrollo,
							 f.TipoDesarrollo,
							 c.cvelote,
							( 	select 	max( DATEDIFF( dd, Vencimiento, @fecha ) )
								from 	vw_enganches
								where 	cvelote = c.cvelote and
									vencimiento < @fecha and
									( pagada in (null,'N','P') or
									  FechaPago > @fecha ) and
									( idtipointercambio is null or
									  idtipointercambio = 'N' ) and
									 DATEDIFF( dd, Vencimiento, @fecha ) >= 3
							 ) as dias_atraso_max,
							 sum( case
								when c.Pagada = 'P' then C.importe-( isnull( c.deposito, 0 ) + 
isnull( c.descuento, 0 ) - isnull( c.moratorios, 0 ) )
								else c.importe
							 end ) as importe
						from	 desarrollos f
								inner join lotes l
								on f.iddesarrollo = l.iddesarrollo
								inner join ventas v
								on l.idlote = v.idlote and
								   v.EstadoActual = 'A'
								inner join Enganches as c
								on c.cvelote = l.idlote
						where	 l.staventa = 'C' and
							 ( c.pagada in (null,'N','P') or
							   c.FechaPago > @fecha ) and
							 ( v.idtipointercambio is null or
							   v.idtipointercambio = 'N' ) and
							 c.fechavento < @fecha and
							 DATEDIFF( dd, c.fechavento, @fecha ) >= 3
						group by f.iddesarrollo,
							 f.TipoDesarrollo,
							 c.cvelote
					) as lote_con_dias_atraso
					where	dias_atraso_max > 60
				) as por_vencer
				on d.cvelote = por_vencer.cvelote
		where	DATEDIFF( dd, fechavento, @fecha ) < 3
		group by iddesarrollo
	 ) as por_vencer
	  group by iddesarrollo
	) por_vencer
where	#tmp_carteraVencida.iddesarrollo collate database_default = por_vencer.iddesarrollo

update	#tmp_carteraVencida
set	importe_monto_vencido = isnull( importe_tipo1, 0 ) + 
isnull(importe_tipo2,0) +isnull(importe_tipo3,0),
	total_porvencer = isnull(importe_tipo3,0) + isnull(importe_tipo4,0)

update	#tmp_carteraVencida
set	suma = isnull( importe_monto_vencido, 0 ) + isnull( importe_tipo4, 0 ),
	cartera_documentada = dbo.fnTotalCarteraDocumentada( iddesarrollo ,0 ),
	cartera_documentadaI = dbo.fnTotalCarteraDocumentada( iddesarrollo ,1 ),
	carteraxcobrar_vigente = dbo.fnCarteraXCobrarVigente( iddesarrollo, 
@fecha, 0 )

update	#tmp_carteraVencida
set	cartera_documentadaT = cartera_documentada + cartera_documentadaI,
	porcentaje_cartera_vencida = (
		case
			when isnull( carteraxcobrar_vigente, 0 ) = 0 then 0
			else ( importe_monto_vencido / carteraxcobrar_vigente )
		end * 100
	),
	porcentaje_cartera_vencida_acumulado = (
		case
			when isnull( carteraxcobrar_vigente, 0 ) = 0 then 0
			else ( suma / carteraxcobrar_vigente )
		end * 100
	),
	porcentaje_cartera_total_documentada = (
		case
			when isnull( cartera_documentada, 0 ) = 0 then 0
			else ( importe_monto_vencido / cartera_documentada )
		end * 100
	),
	porcentaje_creditos_vencidos = (
		case
			when isnull( cartera_documentada, 0 ) = 0 then 0
			else ( suma / cartera_documentada )
		end * 100
	)


select	*
from	#tmp_carteraVencida
order by tipodesarrollo,
	 iddesarrollo

drop table #tmp_carteraVencida
GO
